# 作者: spy
# 开发时间: 2021/12/15 15:34

import os
import openpyxl


class ExcelUtil:

    # 获得项目路径
    def get_object_path(self):
        return os.path.abspath(os.path.dirname(__file__)).split("common")[0]

    def read_excel_login(self):
        # openpyxl,xlrd
        # 加载excel工作簿
        wb = openpyxl.load_workbook(self.get_object_path() + "data/data.xlsx")
        # 获得sheet对象
        sheet = wb['login']
        # 获得excel的行数和列数
        print(sheet.max_row, sheet.max_column)
        # 循环
        all_list = []
        for rows in range(2, sheet.max_row + 1):
            temp_list = []
            for cols in range(1, sheet.max_column + 1):
                temp_list.append(sheet.cell(rows, cols).value)
            all_list.append(temp_list)
        return all_list

    def read_excel_TrailerInfo(self):
        # 加载excel工作簿
        wb = openpyxl.load_workbook(self.get_object_path() + "data/data.xlsx")
        # 获得sheet对象
        sheet = wb['trailer_info']
        # 获得excel的行数和列数
        print(sheet.max_row, sheet.max_column)
        # 循环
        all_list = []
        for rows in range(2, sheet.max_row + 1):
            temp_list = []
            for cols in range(1, sheet.max_column + 1):
                temp_list.append(sheet.cell(rows, cols).value)
            all_list.append(temp_list)
        return all_list

    def read_excel_TrackMonitor(self):
        # 加载excel工作簿
        wb = openpyxl.load_workbook(self.get_object_path() + "data/data.xlsx")
        # 获得sheet对象
        sheet = wb['track_monitor']
        # 获得excel的行数和列数
        print(sheet.max_row, sheet.max_column)
        # 循环
        all_list = []
        for rows in range(2, sheet.max_row + 1):
            temp_list = []
            for cols in range(1, sheet.max_column + 1):
                temp_list.append(sheet.cell(rows, cols).value)
            all_list.append(temp_list)
        return all_list

    def read_excel_RTMessage(self):
        # 加载excel工作簿
        wb = openpyxl.load_workbook(self.get_object_path() + "data/data.xlsx")
        # 获得sheet对象
        sheet = wb['rt_message']
        # 获得excel的行数和列数
        print(sheet.max_row, sheet.max_column)
        # 循环
        all_list = []
        for rows in range(2, sheet.max_row + 1):
            temp_list = []
            for cols in range(1, sheet.max_column + 1):
                temp_list.append(sheet.cell(rows, cols).value)
            all_list.append(temp_list)
        return all_list

# [1,"admin","admin123"],[2,"admin","admin"],[3,"admin123","admin123"]
# [[1, 'admin', 'admin123'], [2, 'admin', 'admin'], [3, 'admin123', 'admin123']]

#
# if __name__ == '__main__':
#     ExcelUtil().read_excel_login()
